<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>The ij tool</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Apache Derby, Java, tutorial, database, ij tool">
<meta name="description" content="This chapter of the Apache Derby tutorial
covers the ij tool.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>The ij tool</h1>

<p>
In the fourth chapter, we are going to look at Derby's ij tool in
a greater detail.
</p>

<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* NewSquare */
google_ad_slot = "0364418177";
google_ad_width = 300;
google_ad_height = 250;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 


<p>
The ij is an interactive scripting tool supplied with Derby. It is a command
line client for the Derby database system. It can be used in two ways. Either
run SQL files or interactively execute SQL statements. The ij is located
in the bin directory of the Derby installation directory.
</p>

<pre>
$ ls $DERBY_HOME/bin | grep ij
ij
ij.bat
</pre>

<p>
There are two scripts. The one with the .bat extension is for Windows.
</p>


<h2>Starting ij</h2>

<p>
The ij can be started in three basic ways. 
</p>

<pre>
$ ij
ij version 10.8
ij> 
</pre>

<p>
Derby ij can be started with the ij script. The above example assumes that we
have the Derby's bin directory in our PATH variable. Otherwise we must use the
full path to the ij tool. 
</p>

<pre>
$ java -cp $DERBY_HOME/lib/derbytools.jar org.apache.derby.tools.ij
ij version 10.8
ij> 
</pre>

<p>
Another way is to execute the compiled Java program. We must have the derbytools.jar 
in our classpath. The first way does essentially the same within the script file. 
It also works with environment variables. 
</p>

<pre>
$ java -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.8
ij> 
</pre>

<p>
In the third way, we use the derbyrun.jar file to start the ij tool. 
</p>

<h2>Running SQL scripts</h2>

<p>
Unlike MySQL or PostgreSQL command line tools, ij is very simplistic. It
does not have a history of commands. Cursor keys cannot be used. It is 
quite unconvenient to use the tool. It is easier to edit SQL statements in
a file and run it with the ij tool. 
</p>

<pre>
$ cat cars.sql 
SET SCHEMA USER12;
CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS VALUES(1, 'Audi', 52642);
INSERT INTO CARS VALUES(2, 'Mercedes', 57127);
INSERT INTO CARS VALUES(3, 'Skoda', 9000);
INSERT INTO CARS VALUES(4, 'Volvo', 29000);
INSERT INTO CARS VALUES(5, 'Bentley', 350000);
INSERT INTO CARS VALUES(6, 'Citroen', 21000);
INSERT INTO CARS VALUES(7, 'Hummer', 41400);
INSERT INTO CARS VALUES(8, 'Volkswagen', 21600);
</pre>

<p>
A CARS table is created in schema USER12 and five rows are inserted.
</p>

<pre>
ij> RUN 'cars.sql';
ij> SET SCHEMA USER12;
0 rows inserted/updated/deleted
ij> CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
0 rows inserted/updated/deleted
ij> INSERT INTO CARS VALUES(1, 'Audi', 52642);
1 row inserted/updated/deleted
...
</pre>

<p>
We use the RUN command to execute the cars.sql file. The file is 
located in the same directory, where ij was launched. 
</p>

<pre>
ij> SELECT * FROM CARS;
ID         |NAME                          |PRICE      
------------------------------------------------------
1          |Audi                          |52642      
2          |Mercedes                      |57127      
3          |Skoda                         |9000       
4          |Volvo                         |29000      
5          |Bentley                       |350000     
6          |Citroen                       |21000      
7          |Hummer                        |41400      
8          |Volkswagen                    |21600      

8 rows selected
</pre>

<p>
We check the data. The table was successfully created.
</p>

<hr class="btm">

<p>
An SQL file name can be taken by the ij tool as a parameter. 
</p>

<pre>
$ cat cars2.sql 
CONNECT 'jdbc:derby:testdb';

SET SCHEMA USER12;
CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS VALUES(1, 'Audi', 52642);
INSERT INTO CARS VALUES(2, 'Mercedes', 57127);
INSERT INTO CARS VALUES(3, 'Skoda', 9000);
INSERT INTO CARS VALUES(4, 'Volvo', 29000);
INSERT INTO CARS VALUES(5, 'Bentley', 350000);
INSERT INTO CARS VALUES(6, 'Citroen', 21000);
INSERT INTO CARS VALUES(7, 'Hummer', 41400);
INSERT INTO CARS VALUES(8, 'Volkswagen', 21600);
</pre>

<p>
We add a CONNECT statement to the cars2.sql file. When we launch
the ij tool, we are not yet connected to the database.
</p>

<pre>
ij> DROP TABLE CARS;
0 rows inserted/updated/deleted
ij> EXIT;
$
</pre>

<p>
We drop the table before we execute the cars2.sql script.
</p>
 
<pre>
$ java -Dderby.system.home=/home/janbodnar/programming/derby/dbs \
> -jar $DERBY_HOME/lib/derbyrun.jar ij cars2.sql
</pre>

<p>
We set the Derby system directory and launch the ij tool with the cars2.sql
as a parameter. The CARS table is created again.
</p>



<h2>Basic commands</h2>

<p>
We can issue two kinds of commands. Commands specific to the ij tool
and SQL statements. Each command in ij is terminated with a semicolon.
All ij commands, identifiers, and keywords are case-insensitive.
</p>

<pre>
ij> HELP;
 
 Supported commands include:
 
  PROTOCOL 'JDBC protocol' [ AS ident ];
                               -- sets a default or named protocol
  DRIVER 'class for driver';   -- loads the named class
  CONNECT 'url for database' [ PROTOCOL namedProtocol ] [ AS connectionName ];
                               -- connects to database URL
                               -- and may assign identifier
...
</pre>

<p>
The HELP command shows a list of ij commands
</p>

<pre>
ij> CONNECT 'jdbc:derby:testdb';
</pre>

<p>
The CONNECT command connects to a database. In our case the database
name is testdb. This example assumes that we have set the Derby system
directory. (More about it in the next section.) Derby by default does
not require a user name and a password. We can configure Derby to require
it.
</p>

<pre>
ij> SHOW CONNECTIONS;
CONNECTION0* -  jdbc:derby:testdb
* = current connection
</pre>

<p>
The <code>SHOW CONNECTIONS</code> statement lists all opened connections.
In our case we can see an opened connection to the testdb database. 
</p>

<pre>
ij> SHOW TABLES;
TABLE_SCHEM         |TABLE_NAME                    |REMARKS             
------------------------------------------------------------------------
SYS                 |SYSALIASES                    |                    
SYS                 |SYSCHECKS                     |                    
SYS                 |SYSCOLPERMS                   | 
...
</pre>

<p>
The <code>SHOW TABLES</code> command shows all tables in a database.
There are a few SYS tables. 
</p>

<pre>
ij> SHOW TABLES IN USER12;
TABLE_SCHEM         |TABLE_NAME                    |REMARKS             
------------------------------------------------------------------------
USER12              |CARS                          |                    

1 row selected
</pre>

<p>
We can list tables in a specific schema. The 
<code>SHOW TABLES IN USER12</code> shows tables in the USER12
schema. 
</p>

<pre>
ij> DESCRIBE USER12.CARS;
COLUMN_NAME         |TYPE_NAME|DEC&amp;|NUM&amp;|COLUM&amp;|COLUMN_DEF|CHAR_OCTE&amp;|IS_NULL&amp;
------------------------------------------------------------------------------
ID                  |INTEGER  |0   |10  |10    |NULL      |NULL      |NO      
NAME                |VARCHAR  |NULL|NULL|30    |NULL      |60        |YES     
PRICE               |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     

3 rows selected
</pre>

<p>
The DESCRIBE command provides a decription of the specified table or view. 
If the USER12 schema is not the current schema of the connection, we have to specify
it before the table name. The current schema is specified in the connection string
as the user name. 
</p>

<pre>
ij> DISCONNECT;
ij> SHOW CONNECTIONS;
No connections available.
</pre>

<p>
The DISCONNECT command disconnects from the database.
The subsequent SHOW CONNECTIONS command shows no available connections. 
</p>

<pre>
ij> EXIT;
$ 
</pre>

<p>
Finally, we exit the ij tool. In case of an embedded environment, it also
shuts down the database. Which is equivalent to the 
CONNECT 'jdbc:derby:testdb;shutdown=true'; command. 
</p>

<hr class="btm">

<p>
The main purpose of the ij tool is to issue SQL commands. We reconnect to the
testdb database. 
</p>

<pre>
$ ij
ij version 10.8
ij> CONNECT 'jdbc:derby:testdb;user=USER12';
</pre>

<p>
Now the current schema is the USER12 schema. When issuing SQL statements for tables 
located in the USER12 schema, we can omit the schema name.
</p>

<pre>
ij> SELECT * FROM CARS WHERE ID IN (1, 3, 5);
ID         |NAME                          |PRICE      
------------------------------------------------------
1          |Audi                          |52642      
3          |Skoda                         |9000       
5          |Bentley                       |350000     

3 rows selected
</pre>

<p>
In the above SQL statement, we select all three columns for rows with
IDs 1, 3 and 5.
</p>

<pre>
ij> SELECT * FROM APP.FRIENDS;
ID         |NAME                     
-------------------------------------
1          |Jane                     
2          |Thomas                   
3          |Beka                     

3 rows selected
</pre>

<p>
Three rows from the FRIENDS table were selected. Since the table is not located
in the current schema, we must fully qualify the table name. 
</p>

<pre>
ij> INSERT INTO APP.FRIENDS(ID, NAME) VALUES(4, 'Robert');
1 row inserted/updated/deleted
ij> SELECT ID, NAME FROM APP.FRIENDS WHERE ID=4;
ID         |NAME                     
-------------------------------------
4          |Robert                   

1 row selected
</pre>

<p>
We insert a new row into the FRIENDS table and later select it. 
</p>


<h2>Specifying the Derby system directory</h2>

<p>
Each time we work with Derby, we have to provide the Derby system directory.
In this directory we can find our database files, configuration data or a
log file. If we do not provide a Derby system directory a current directory
is assumed to be the Derby system one. To provide the Derby system directory,
we assign the <code>derby.system.home</code> property. It can be done via
JVM option, configuration file, environment variable or in a Java file. 
</p>

<pre>
$ ls
cars.sql   dbs  doc~  ij.properties  links   
cars.sql~  doc  dump  lib  
</pre>

<p>
The current working directory has a subdirectory called dbs.
We want this directory to be our Derby system directory. 
The current working directory is the place from where we
launch the ij tool. 
</p>


<pre>
$ java -Dderby.system.home=/home/janbodnar/programming/derby/dbs \
> -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.8
</pre>

<p>
Here we specify the Derby system directory with the -D JVM option.
The derby.log file is created in the system directory. In our case
the log file should appear in the dbs directory. The derby.log
file is recreated each time we connect to a Derby database. We can 
look at the timestamp. If the derby.log file appears ouside the intended
directory, we have not set the Derby system directory correctly.
</p>

<pre>
ij> CONNECT 'jdbc:derby:testdb';
ij> SHOW CONNECTIONS;
CONNECTION0* -  jdbc:derby:testdb
* = current connection
</pre>

<p>
Since we have correctly set the Derby system directory, we only
specify the database name in the connection URL and omit the full path. 
The <code>SHOW CONNECTIONS</code> shows the opened connection.
</p>

<hr class="btm">

<p>
We might not want to specify the Derby system directory each time.
We could utilize the DERBY_OPTS environment variable. 
</p>

<pre>
$ echo $DERBY_OPTS
-Dderby.system.home=/home/janbodnar/programming/derby/dbs

$ ij
ij version 10.8
ij> CONNECT 'jdbc:derby:testdb';
ij> SHOW CONNECTIONS;
CONNECTION0* -  jdbc:derby:testdb
* = current connection
</pre>

<p>
Creating a connection to a Derby database is now a bit easier. We save
a few key strokes. 
</p>


<h2>The ij properties</h2>

<p>
When starting up the ij tool, we can specify properties on the command line or
in a properties file. The properties are various parameters taken by the ij tool.
They can save us some repetitive work.
</p>

<pre>
$ java -Dij.user=USER12 -Dij.database=testdb -Dij.protocol=jdbc:derby: \
> -Dderby.system.home=/home/janbodnar/programming/derby/dbs \ 
> -jar $DERBY_HOME/lib/derbyrun.jar ij
ij version 10.8
CONNECTION0* -  jdbc:derby:testdb
* = current connection
ij> SELECT * FROM CARS WHERE ID = 1;
ID         |NAME                          |PRICE      
------------------------------------------------------
1          |Audi                          |52642      

1 row selected
ij>
</pre>

<p>
We provide three ij properties on the command line with the -D option. The ij.user
specifies the user name to establish a connection. The supplied user name becomes
the current schema. The ij.database has the database name to which we connect.
The ij.protocol property specifies the default protocol and subprotocol portions 
of the database connection URL. We are ready to launch SQL statements. 
</p>

<hr class="btm">

<p>
In the next example, we create an ij.properties file, where we set three ij properties. 
</p>

<pre>
$ cat ij.properties 
ij.user=USER12
ij.database=testdb
ij.protocol=jdbc:derby:
</pre>

<p>
With the cat command, we show the contents of the ij.properties file. We set the
same properties as in the first example.
</p>

<pre>
$ java -Dderby.system.home=/home/janbodnar/programming/derby/dbs \
> -jar $DERBY_HOME/lib/derbyrun.jar ij -p ij.properties
ij version 10.8
CONNECTION0* -  jdbc:derby:testdb
* = current connection
ij> SELECT * FROM CARS WHERE ID=2;
ID         |NAME                          |PRICE      
------------------------------------------------------
2          |Mercedes                      |57127      

1 row selected
</pre>

<p>
The -p option of the ij tool takes the properties file name. 
</p>

<p>
In the chapter, we have covered the ij tool. 
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified March 3, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

